package com.adk.backend.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Excel工具类
 */
@Component
public class ExcelUtil {
    
    /**
     * 导出Excel
     */
    public void exportExcel(HttpServletResponse response, String fileName, String[] headers, List<Map<String, Object>> data) throws IOException {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        
        // 创建样式
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        
        // 创建表头
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 填充数据
        int rowNum = 1;
        for (Map<String, Object> row : data) {
            Row dataRow = sheet.createRow(rowNum++);
            int colNum = 0;
            for (String header : headers) {
                Cell cell = dataRow.createCell(colNum++);
                Object value = row.get(header);
                if (value != null) {
                    cell.setCellValue(value.toString());
                }
            }
        }
        
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        
        // 写入响应流
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }
    
    /**
     * 读取Excel文件（XLSX格式）
     * @param file Excel文件
     * @return 数据列表，每行数据为一个List<String>
     */
    public List<List<String>> readExcel(MultipartFile file) throws IOException {
        List<List<String>> data = new ArrayList<>();
        
        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        
        DataFormatter formatter = new DataFormatter();
        
        for (Row row : sheet) {
            List<String> rowData = new ArrayList<>();
            for (Cell cell : row) {
                String cellValue = formatter.formatCellValue(cell);
                rowData.add(cellValue);
            }
            data.add(rowData);
        }
        
        workbook.close();
        inputStream.close();
        
        return data;
    }
    
    /**
     * 将Excel日期转换为Java Date
     */
    public Date excelDateToJavaDate(double excelDate) {
        // Excel日期从1900-01-01开始，但有一个bug（认为是1900年是闰年）
        // 需要减去2天来修正
        long javaTime = (long) ((excelDate - 25569) * 86400 * 1000);
        return new Date(javaTime);
    }
}


